﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;

namespace SqliteWrapper.Query
{
    public class Q_Comment : IQuery
    {
        public override SqliteError Query(out System.Data.DataTable DataSource, string DBPath, List<System.Data.SQLite.SQLiteParameter> parameters, int? startIndex, int? length, DateTime? startTime, DateTime? endTime, QueryOrder order)
        {
            DataSource = null;
            SqliteError error = new SqliteError() { ErrorText = null, IsProcessSuccess = true };
            SqliteConnectionManager manager = SqliteConnectionManager.Create(DBPath);
            using (SQLiteWriteLock locker = new SQLiteWriteLock(manager.ConnectionString))
            {
                using (SQLiteConnection conn = manager.GetSqliteConnection())
                {
                    conn.Open();
                    using (SQLiteCommand query = conn.CreateCommand())
                    {
                        QueryBuilder q_builder = new QueryBuilder("Comment C", "C.ID as commentId", "User.Nick as Nickname", "Book.Title as BookTitle", "C.Comment as Comment", "C.CreateTimeTag as CommentDate", "j.Nick as RefererName", "j.comment as RefererComment", "j.CreateTimeTag as RefererDate")
                        {
                            StartIndex = startIndex,
                            Length = length,

                            StartTime = startTime,
                            EndTime = endTime,
                            Parameters = parameters,
                            OrderBy = order
                  
                        };
                        q_builder.addJoin(new QueryJoin("User",
                                                        JoinMethod.Inner,
                                                        "User.ID = C.UID"
                                                        ));
                        q_builder.addJoin(new QueryJoin("Book",
                                                        JoinMethod.Inner,
                                                        "Book.ID = C.BID"
                                                        ));
                        q_builder.addJoin(new QueryJoin("(select C.ID as commentId,User.Nick,Book.Title,C.comment,C.CreateTimeTag from Comment C " +
                                                        "inner join User on User.id = c.uid " + 
                                                        "inner JOIN Book on Book.id = c.bid) j ",
                                                        JoinMethod.Inner,
                                                        "j.commentId = C.refererId"
                                                        ));
                        query.CommandText = q_builder.BuildSelect();
                        try
                        {
                            using (SQLiteDataReader dr = query.ExecuteReader())
                            {
                                DataSource = q_builder.GetResult(dr);
                            }
                            error.IsProcessSuccess = true;
                        }
                        catch (Exception ex)
                        {
                            error.ErrorText = ex.Message;
                        }
                    }
                }
            }
            return error;
        }
    }
}
